﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CANDataIO
{
    public abstract class ExcelBase : IExcel
    {
        public abstract void Save(string FileName, DataTable data, string SheetName,bool readOnly);
        public abstract void Load(string FileName, out DataTable data, string SheetName, bool ShowLog = false);

        protected void SaveBaseSub(ISheet isheet, System.Data.DataTable data)
        {
            IRow row;
            ICell cell;
            int rowIndex = 0;
            int cellIndex = 0;
            int RowCount = data.Rows.Count;
            int ColumnCount = data.Columns.Count;

            for (rowIndex = 0; rowIndex < data.Rows.Count; rowIndex++)
            {
                row = isheet.CreateRow(rowIndex);//这个函数是创建该工作簿的第rowIndex行，并不是创建rowIndex行，从第0行开始    
                for (cellIndex = 0; cellIndex < ColumnCount; cellIndex++)
                {
                    cell = row.CreateCell(cellIndex);//这个函数是创建该工作簿第cellIndex列（即某个单元格），从第0列开始
                    var cellData = (string)data.Rows[rowIndex][cellIndex];
                    cell.SetCellValue(cellData);//这个函数就是向第rowIndex行和第cellIndex列放数据
                }
            }
        }

        protected void LoadBaseSub(ISheet sheet, System.Data.DataTable data, bool ShowLog = false)
        {
            IRow rowH = sheet.GetRow(0);
            if (rowH == null)
                return;
            // DataTable 创建表架构
            for (int i = 0; i < rowH.Cells.Count; i++)
            {
                string Ti = rowH.GetCell(i).ToString();
                data.Columns.Add(Ti, typeof(string));
            }

            StringBuilder sb = new StringBuilder();
            //遍历表中所有的行
            //注意这里加1，这里得到的最后一个单元格的索引默认是从0开始的
            for (int i = 0; i < sheet.LastRowNum; i++)  //对工作表每一行 
            {
                IRow row = sheet.GetRow(i);   //row读入第i行数据 
                System.Data.DataRow dataRow = data.NewRow();
                if (row != null)
                {
                    for (int j = 0; j < row.LastCellNum; j++)  //对工作表每一列 
                    {
                        //获取第i行第j列数据  其数据是通过返回string类型的来获取
                        string cellValue = row.GetCell(j).ToString();
                        dataRow.ItemArray[j] = cellValue;
                        if (ShowLog)
                            sb.Append(cellValue + ",");
                    }
                    data.Rows.Add(dataRow);
                    if (ShowLog)
                    {
                        Console.WriteLine(sb.ToString());
                        sb.Clear();
                    }
                }
            }
        }

        protected static void SetFileReadAccess(string FileName, bool SetReadOnly)
        {
            FileInfo fInfo = new FileInfo(FileName);
            fInfo.IsReadOnly = SetReadOnly;
        }

        protected static bool IsFileReadOnly(string FileName)
        {
            FileInfo fInfo = new FileInfo(FileName);
            return fInfo.IsReadOnly;
        }
    }



    public class Excel2010 : ExcelBase
    {
        public override void Save(string FileName, System.Data.DataTable data, string SheetName,bool readOnly)
        {
            XSSFWorkbook wk = new XSSFWorkbook();//这是用于后缀名是.xls的excel文件的操作
            ISheet isheet = wk.CreateSheet(SheetName);//这是创建一个工作簿，

            SaveBaseSub(isheet, data);

            FileStream fs2 = new FileStream(FileName, FileMode.OpenOrCreate);
            wk.Write(fs2);
            wk.Close();//这就是释放资源
            fs2.Close();
            fs2.Dispose();
            SetFileReadAccess(FileName, readOnly);
        }

        public override void Load(string FileName, out System.Data.DataTable data, string SheetName, bool ShowLog = false)
        {
            data = null;
            if (System.IO.File.Exists(FileName) == false)
            {
                return;
            }
            if (Path.GetExtension(FileName.ToLower()) != ".xlsx")
            {
                return;
            }

            using (FileStream fs = File.OpenRead(FileName))
            {
                IWorkbook workbook = null;
                workbook = new XSSFWorkbook(fs);

                //因为Excel表中可能不止一个工作簿，其工作簿也是从0开始
                //工作簿位置为0时，其在excel表的最左边  workbook.GetSheetAt(h);
                ISheet sheet = workbook.GetSheet(SheetName);
                if (sheet == null)
                    return;

                data = new System.Data.DataTable(SheetName);
                LoadBaseSub(sheet, data, ShowLog);

            }// End using

        }

    }
}
